
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 11/01/2012 15:50:22
-- Generated from EDMX file: D:\team-hotel-project\src\Hotel.llc\Hotel.Mudule.Resources\DataRepository\ResourceEntityModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [ManagementDatabae];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_RoomCard]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Cards] DROP CONSTRAINT [FK_RoomCard];
GO
IF OBJECT_ID(N'[dbo].[FK_RoomPeriod]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Periods] DROP CONSTRAINT [FK_RoomPeriod];
GO
IF OBJECT_ID(N'[dbo].[FK_HallPeriod]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Periods] DROP CONSTRAINT [FK_HallPeriod];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Rooms]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Rooms];
GO
IF OBJECT_ID(N'[dbo].[Periods]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Periods];
GO
IF OBJECT_ID(N'[dbo].[Halls]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Halls];
GO
IF OBJECT_ID(N'[dbo].[Cards]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Cards];
GO
IF OBJECT_ID(N'[dbo].[Services]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Services];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Rooms'
CREATE TABLE [dbo].[Rooms] (
    [Number] int IDENTITY(1,1) NOT NULL,
    [Capacity] int  NOT NULL,
    [Type] int  NOT NULL,
    [State] int  NOT NULL
);
GO

-- Creating table 'Periods'
CREATE TABLE [dbo].[Periods] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [OrderID] int  NOT NULL,
    [OutDate] datetime  NOT NULL,
    [InDate] datetime  NOT NULL,
    [RoomNumber] int  NULL,
    [HallNumber] int  NULL
);
GO

-- Creating table 'Halls'
CREATE TABLE [dbo].[Halls] (
    [Number] int IDENTITY(1,1) NOT NULL,
    [Type] int  NOT NULL,
    [State] int  NOT NULL
);
GO

-- Creating table 'Cards'
CREATE TABLE [dbo].[Cards] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [ExpireDate] datetime  NOT NULL,
    [OrderID] int  NOT NULL,
    [State] int  NOT NULL,
    [RoomNumber] int  NOT NULL
);
GO

-- Creating table 'Services'
CREATE TABLE [dbo].[Services] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Cost] float  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [OrderID] int  NOT NULL,
    [Rated] bit  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Number] in table 'Rooms'
ALTER TABLE [dbo].[Rooms]
ADD CONSTRAINT [PK_Rooms]
    PRIMARY KEY CLUSTERED ([Number] ASC);
GO

-- Creating primary key on [Id] in table 'Periods'
ALTER TABLE [dbo].[Periods]
ADD CONSTRAINT [PK_Periods]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Number] in table 'Halls'
ALTER TABLE [dbo].[Halls]
ADD CONSTRAINT [PK_Halls]
    PRIMARY KEY CLUSTERED ([Number] ASC);
GO

-- Creating primary key on [Id] in table 'Cards'
ALTER TABLE [dbo].[Cards]
ADD CONSTRAINT [PK_Cards]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Services'
ALTER TABLE [dbo].[Services]
ADD CONSTRAINT [PK_Services]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [RoomNumber] in table 'Cards'
ALTER TABLE [dbo].[Cards]
ADD CONSTRAINT [FK_RoomCard]
    FOREIGN KEY ([RoomNumber])
    REFERENCES [dbo].[Rooms]
        ([Number])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RoomCard'
CREATE INDEX [IX_FK_RoomCard]
ON [dbo].[Cards]
    ([RoomNumber]);
GO

-- Creating foreign key on [RoomNumber] in table 'Periods'
ALTER TABLE [dbo].[Periods]
ADD CONSTRAINT [FK_RoomPeriod]
    FOREIGN KEY ([RoomNumber])
    REFERENCES [dbo].[Rooms]
        ([Number])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RoomPeriod'
CREATE INDEX [IX_FK_RoomPeriod]
ON [dbo].[Periods]
    ([RoomNumber]);
GO

-- Creating foreign key on [HallNumber] in table 'Periods'
ALTER TABLE [dbo].[Periods]
ADD CONSTRAINT [FK_HallPeriod]
    FOREIGN KEY ([HallNumber])
    REFERENCES [dbo].[Halls]
        ([Number])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_HallPeriod'
CREATE INDEX [IX_FK_HallPeriod]
ON [dbo].[Periods]
    ([HallNumber]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------